﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using CRUD_ADO.Net.Entity;

namespace CRUD_ADO.Net.DAO
{
    public class DAODemo
    {
        private SQLConntectionDB SqlCnn;
        public DataTable SelectData()
        {
            SqlCnn = new SQLConntectionDB();
            string query = "Select CustomerID,CompanyName,ContactName,[Address],Phone from Customers";
            return SqlCnn.selectQuery_ExecuteReader(query);
        }
        public bool insertData(Customer customer)
        {
            SqlCnn = new SQLConntectionDB();
            SqlParameter[] myparameter = new SqlParameter[5];
            string sqlQuery = "INSERT INTO Customers (CustomerID,CompanyName,ContactName,Address,Phone) values (@CustomerID,@CompanyName,@ContactName,@Address,@Phone)";
            string CustomerId = customer.CustomerID1;
            string CompanyName = customer.CompanyName1;
            string ContactName = customer.ContactName1;
            string Address = customer.Address1;
            string Phone = customer.Phone1;

            myparameter[0] = new SqlParameter("@CustomerID", SqlDbType.NChar, 5);
            myparameter[0].Value = CustomerId;

            myparameter[1] = new SqlParameter("@CompanyName", SqlDbType.NVarChar, 40);
            myparameter[1].Value = CompanyName;

            myparameter[2] = new SqlParameter("@ContactName", SqlDbType.NVarChar, 30);
            myparameter[2].Value = ContactName;

            myparameter[3] = new SqlParameter("@Address", SqlDbType.NVarChar, 60);
            myparameter[3].Value = Address;

            myparameter[4] = new SqlParameter("@Phone", SqlDbType.NVarChar, 24);
            myparameter[4].Value = Phone;

            return SqlCnn.insertQuery_NonQuery(sqlQuery, myparameter);
        }

        public bool deleteData(string CustomerID)
        {
            SqlCnn = new SQLConntectionDB();
            string sql = "DELETE FROM Customers WHERE CustomerID =@CustomerId";
            SqlParameter[] myParameter = new SqlParameter[1];
            myParameter[0] = new SqlParameter("CustomerId", SqlDbType.NChar, 5);
            myParameter[0].Value = CustomerID;

            return SqlCnn.insertQuery_NonQuery(sql, myParameter);
        }

        public bool updateData(Customer customer)
        {
            SqlCnn = new SQLConntectionDB();
            string sqlQuery = "UPDATE Customers SET CompanyName=@CompanyName,ContactName =@ContactName,[Address] =@Address,Phone=@Phone WHERE CustomerID = @CustomerId";
            SqlParameter[] myparameter = new SqlParameter[5];
            string CustomerId = customer.CustomerID1;
            string CompanyName = customer.CompanyName1;
            string ContactName = customer.ContactName1;
            string Address = customer.Address1;
            string Phone = customer.Phone1;

            myparameter[0] = new SqlParameter("@CustomerId", SqlDbType.NChar, 5);
            myparameter[0].Value = CustomerId;

            myparameter[1] = new SqlParameter("@CompanyName", SqlDbType.NVarChar, 40);
            myparameter[1].Value = CompanyName;

            myparameter[2] = new SqlParameter("@ContactName", SqlDbType.NVarChar, 30);
            myparameter[2].Value = ContactName;

            myparameter[3] = new SqlParameter("@Address", SqlDbType.NVarChar, 60);
            myparameter[3].Value = Address;

            myparameter[4] = new SqlParameter("@Phone", SqlDbType.NVarChar, 24);
            myparameter[4].Value = Phone;

            return SqlCnn.insertQuery_NonQuery(sqlQuery, myparameter);

        }

        public int CountItem()
        {
            SqlCnn = new SQLConntectionDB();
            string SqlQuery = "SELECT Count (*) FROM Customers";
            return SqlCnn.countQuery_ExecuteScalar(SqlQuery);
        }

    }
}